﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;

namespace Framework.DataAccess
{

    public class ModeAdpater
    {
        private readonly DbInstance db;
        public ModeAdpater()
        {
            db = DbManager.GetDbInstance();
        }

        // 获取模型的创建考场的次数
        private int GetModelExam(int mID)
        {
            string strSql = "SELECT COUNT(*) FROM examination_table WHERE model = " + mID;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);

            return rowCount;
        }

        /// <summary>
        /// 获取模型的角色名称
        /// </summary>
        /// <param name="mID"></param>
        /// <returns></returns>
        //public string GetModelRoleName(int mID)
        //{
        //    string strSql = "declare @roleName varchar(500);\r\n set @roleName='';\r\n";
        //    strSql += "Select @roleName=@roleName+name+',' from role_table where id in(Select [role] from are_role_join where id in" +
        //        "(Select areRoleID From model_are_role where modelID={0}))group by name;\r\n Select @roleName as roleName";
        //    strSql = string.Format(strSql, mID);
        //    return db.ExecuteScalarBySql(strSql).ToStr();
        //}

        public string GetModelRoleName(int mID)
        {
            Hashtable hh = new Hashtable();
            string sql = "select * from are_role_view join model_are_role on are_role_view.J_ID = model_are_role.areRoleID where modelID=" + mID;
            DataTable dt = db.ExecuteDataTableBySql(sql);
            foreach (DataRow row in dt.Rows)
            {
                string are_name = row["A_NAME"].ToStr();
                string role_name = row["R_NAME"].ToStr();

                if (hh.ContainsKey(are_name))
                    hh[are_name] += ","+role_name;
                else
                    hh.Add(are_name, role_name);
            }

            StringBuilder sb = new StringBuilder();
            System.Collections.IDictionaryEnumerator enumerator = hh.GetEnumerator();
            while (enumerator.MoveNext())
                sb.Append(enumerator.Key.ToStr() + "&nbsp;:&nbsp;[" + enumerator.Value +"]&nbsp;,&nbsp;" );


            return sb.ToString();
        }


        // 取得导出的Excel
        public PageList<object> GetExcel(int status,
                          int type,
                          int? areid = null,
                          int? roleid = null,
                          DateTime? beginDate = null,
                          DateTime? endDate = null,
                          string title = "")
        {
            string strWhere = " WHERE 1=1 ";
            if (areid.HasValue)
                if (areid.Value > 0)
                    strWhere += string.Format(" And ([id] in(Select modelID From model_are_role Where areRoleID In(Select id From are_role_join Where are={0})))", areid.Value);
            if (roleid.HasValue)
                if (roleid.Value > 0)
                    strWhere += string.Format(" And ([id] in(Select modelID From model_are_role Where areRoleID In(Select id From are_role_join Where role={0})))", roleid.Value);
            if (type == 0 || type == 1)
                strWhere += string.Format(" And [type]={0}", type);
            if (beginDate.HasValue)
                strWhere += string.Format(" And [date]>='{0}'", beginDate.Value.ToString("yyyy-MM-dd"));
            if (endDate.HasValue)
                strWhere += string.Format(" And [date]<='{0} 23:59'", endDate.Value.ToString("yyyy-MM-dd"));
            if (title.Length > 0)
                strWhere += string.Format(" And [name] Like '%{0}%'", title.Replace("'", "''"));
            if (status == 0 || status == 1)
                strWhere += string.Format(" And [status]={0}", status);

            PageList<object> list = new PageList<object>();

            //string strSql = "SELECT  COUNT(*) as Row FROM  model_table " + strWhere;
            //int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            //string orderby = " Order By [id] ";
            //if (pageInfo.SortName != "")
            //    orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            string strSql = "SELECT {0} From {1} {2} ";
            strSql = string.Format(strSql, " id, name, type, date, status, model_score,has_QA "
                            , "model_table", strWhere);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            dt.Columns.Add("exam", typeof(int));
            dt.Columns.Add("rolename", typeof(string));
            dt.Columns.Add("arename", typeof(string));
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                int modelID = dt.Rows[index]["id"].ToInt();
                dt.Rows[index]["exam"] = GetModelExam(modelID);
                dt.Rows[index]["rolename"] = GetModelRoleName(modelID);
            }

            list.Data = dt;
            return list;
        }

        //  取得模型列表的JSON
        public PageList<object> GetGridJson(PageView pageInfo, 
                          int status,
                          int type,
                          int? areid=null, 
                          int? roleid=null, 
                          DateTime? beginDate = null, 
                          DateTime? endDate = null, 
                          string title = "" )
        {
            string strWhere = " WHERE 1=1 ";
            if (areid.HasValue)
                if (areid.Value > 0)
                    strWhere += string.Format(" And ([id] in(Select modelID From model_are_role Where areRoleID In(Select id From are_role_join Where are={0})))", areid.Value);
            if (roleid.HasValue)
                if (roleid.Value > 0)
                    strWhere += string.Format(" And ([id] in(Select modelID From model_are_role Where areRoleID In(Select id From are_role_join Where role={0})))", roleid.Value);
            if (type == 0 || type == 1)
                    strWhere += string.Format(" And [type]={0}", type);
            if (beginDate.HasValue)
                strWhere += string.Format(" And [date]>='{0}'", beginDate.Value.ToString("yyyy-MM-dd"));
            if (endDate.HasValue)
                strWhere += string.Format(" And [date]<='{0} 23:59'", endDate.Value.ToString("yyyy-MM-dd"));
            if (title.Length > 0)
                strWhere += string.Format(" And [name] Like '%{0}%'", title.Replace("'", "''"));
            if (status == 0  || status == 1)
                strWhere += string.Format(" And [status]={0}", status);

            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  COUNT(*) as Row FROM  model_table " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By [id] ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 ";
            strSql = string.Format(strSql, orderby, " id, name, type, date, status, model_score,has_QA "
                            , "model_table", strWhere);

            if (areid.HasValue && areid.Value > -1)
                strSql += String.Format(" Where rows Between {0} And {1}", ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            dt.Columns.Add("exam", typeof(int));
            dt.Columns.Add("rolename", typeof(string));
            dt.Columns.Add("arename", typeof(string));
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                int modelID = dt.Rows[index]["id"].ToInt();
                dt.Rows[index]["exam"] = GetModelExam(modelID);
                dt.Rows[index]["rolename"] = GetModelRoleName(modelID);
            }

            list.Total = rowCount;
            list.Data = dt;

            return list;
        }



        public bool SetModelStatus(int modelID, int status)
        {
            string strSql = string.Format("Update model_table Set status={0} Where [id]={1}", status, modelID);
            int count = db.ExecuteNonQueryBySql(strSql);
            return count == 1;
        }

        /// <summary>
        /// 保存模型
        /// </summary>
        /// <param name="modelData"></param>
        /// <param name="modelDetList"></param>
        /// <param name="areRoleIDList">区域角色ID列表</param>
        public int AddModel(model_table modelData, List<compute_table> modelDetList, List<int> areRoleIDList)
        {
            string strSql = "";
            DbManager.BeginTransaction();
            DbInstance myDB = DbManager.GetDbInstance();
            try
            {
                //增加主表
                strSql = "Insert Into model_table([name],[type],[date],[status],model_score,has_QA)Values(@name,{0},getdate(),{1},{2},{3});Select Scope_Identity()As modelID;\r\n";
                strSql = string.Format(strSql, modelData.type, modelData.status, modelData.model_score, modelData.has_QA ? "1" : "0");
                DbCommand cmd = myDB.GetSqlStringCommand(strSql);
                db.AddInParameter(cmd, "@name", DbType.String, modelData.name);
                object obj = db.ExecuteScalar(cmd);
                if (obj == null)
                {
                    DbManager.Rollback();
                    return 0;
                }
                int modelID = Convert.ToInt32(obj);
                //增加子表
                foreach (compute_table modelDet in modelDetList)
                {
                    modelDet.model = modelID;
                    AddModelDet(modelDet, myDB);
                }
                //增加区域角色
                strSql = "";
                foreach (int areRoleID in areRoleIDList)
                {
                    strSql += string.Format("Insert Into model_are_role(modelID,areRoleID)Values({0},{1});\r\n",modelID, areRoleID);
                }
                if (strSql != "")
                    db.ExecuteNonQueryBySql(strSql);
                //提交事务
                DbManager.Commit();
                return modelID;
            }
            catch (Exception e)
            {
                DbManager.Rollback();
                throw e;
            }
        }

        /// <summary>
        /// 增加模型子表
        /// </summary>
        /// <param name="modelDet"></param>
        private int AddModelDet(compute_table modelDet, DbInstance myDB)
        {
            string strSql = "Insert Into compute_table([type],rolecount,publiccount,topic,score,model)Values({0},{1},{2},{3},{4},{5});Select Scope_Identity()As detID;";
            strSql = string.Format(strSql, modelDet.type, modelDet.rolecount, modelDet.publiccount, modelDet.topic, modelDet.score, modelDet.model);
            object obj = myDB.ExecuteScalarBySql(strSql);
            if (obj != null)
                return 0;
            else
                return obj.ToInt(0);
        }

        /// <summary>
        /// 根据获取随机模型
        /// </summary> 
        /// <param name="modelID"></param>
        /// <returns></returns>
        public PageList<object> GetRandomModelDetList(PageView pageInfo, int modelID)
        {
            string strWhere = " WHERE model=" + modelID;
            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  COUNT(*) as Row FROM  compute_table " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By [type] ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where rows Between {4} And {5}";
            strSql = string.Format(strSql, orderby, "*", "compute_table", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);

            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        /// <summary>
        /// 获取手动模型
        /// </summary>
        /// <param name="modelID"></param>
        /// <returns></returns>
        public PageList<object> GetManualModelDetList(PageView pageInfo, int modelID)
        {
            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  COUNT(*) as Row FROM  compute_table WHERE model=" + modelID;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By [type] ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2})As T0 Where rows Between {3} And {4}";
            string view = string.Format("(Select A.*,B.title,B.date From compute_table As A inner join topic_table As B On(A.topic=B.id) Where A.model={0} And A.topic<>0)As v0 ", modelID);
            strSql = string.Format(strSql, orderby, "*", view, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);

            list.Total = rowCount;
            list.Data = dt;

            return list;
        }
    }

    class Model : Mode.model_table
    {
        public int areid { get; set; }
        public string arename { get; set; }
        public int roleid { get; set; }
        public string rolename { get; set; }
    }

}
